﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Reflection;
using System.Text;
using AkazoNobel.Scheduler.StoreMasterData.Common;
using AkazoNobel.Scheduler.StoreMasterData.DataAccess.Model;
using Dapper;
using log4net;
using log4net.Repository.Hierarchy;
using System.Data;

namespace AkazoNobel.Scheduler.StoreMasterData.DataAccess.Service
{
    public static class CustomerService
    {
        /// <summary>
        /// 日志记录
        /// </summary>
        private static readonly ILog Logger = LogManager.GetLogger(MethodBase.GetCurrentMethod().DeclaringType);

        public static IEnumerable<Dealer> GetModifiedList()
        {
            List<Customer> customers;

            //1. 从MDC数据库中抓取最新的数据
            using (var conn = SqlConnectionHelper.GetConnection(Constant.MDC_DB_NAME))
            {
                customers = conn.Query<Customer>("SELECT * FROM m_Customer ORDER BY ID", null).ToList();
            }

            //2. 比对SMD数据库中的数据，看是否相同
            var results = new List<Dealer>();
            using (var connSmd = SqlConnectionHelper.GetConnection(Constant.SMD_DB_NAME))
            {
                foreach (var customer in customers)
                {
                    var dealer = connSmd.Query<Dealer>(
                        "SELECT TOP 1 * FROM t_smd_Dealer WHERE DealerCode=@DealerCode",
                        new { DealerCode = customer.CustomerCode }).SingleOrDefault();

                    if (dealer == null) //如果主数据库中没有这个经销商，就插入
                    {
                        var salesOffice = CustomerService.GetSalesOffice(customer.CityID);
                        if (salesOffice == null)
                        {
                            continue;
                        }

                        dealer = new Dealer()
                        {
                            DealerCode = customer.CustomerCode,
                            Name = customer.CustomerName,
                            Region = salesOffice.Region,
                            Province = salesOffice.Province,
                            CityName = salesOffice.CityName
                        };
                        connSmd.Execute("INSERT INTO t_smd_Dealer (DealerCode, Name, Region, Province, CityName) VALUES(@DealerCode, @Name, @Region, @Province, @CityName)", dealer);
                        
                        Logger.Debug("新增一个供应商：" + customer.CustomerCode + " " + customer.CustomerName);
                    }
                    else if (dealer.Name != customer.CustomerName) //如果名称更新过了
                    {
                        //更新名称
                        connSmd.Execute("UPDATE t_smd_Dealer SET Name=@Name WHERE DealerID=@DealerID",
                            new { Name = customer.CustomerName, DealerID = dealer.DealerID });

                        Logger.Debug("供应商变更：" + customer.CustomerCode + " " + customer.CustomerName);

                        dealer.CityName = customer.CityID;
                        results.Add(dealer);
                    }
                }
            }

            return results;
        }

        public static SalesOffice GetSalesOffice(string city)
        {
            using (var conn = SqlConnectionHelper.GetConnection(Constant.MDC_DB_NAME))
            {
                var salesOffices = conn.Query<SalesOffice>(
                    "SELECT Sales_Office AS Region, Region_Code_Name AS Province, City AS CityName FROM m_Sales_Office WHERE City_Code=@city", 
                    new {city}).ToList();

                foreach (var salesOffice in salesOffices)
                {
                    if (string.IsNullOrEmpty(salesOffice.Region) || string.IsNullOrEmpty(salesOffice.Province))
                    {
                        continue;
                    }

                    if (salesOffice.Region.ToUpper().Contains("EAST"))
                    {
                        salesOffice.Region = "EAST";
                    }
                    else if (salesOffice.Region.ToUpper().Contains("WEST"))
                    {
                        salesOffice.Region = "WEST";
                    }
                    else if (salesOffice.Region.ToUpper().Contains("NORTH"))
                    {
                        salesOffice.Region = "NORTH";
                    }
                    else if (salesOffice.Region.ToUpper().Contains("SOUTH"))
                    {
                        salesOffice.Region = "SOUTH";
                    }
                    else
                    {
                        continue;
                    }

                    salesOffice.Province = salesOffice.Province.Split('_')[2];

                    return salesOffice;
                }
                return null;
            }
        }


        /// <summary>
        /// 负责销售同步
        /// </summary>
        /// <param name="Type"></param>
        /// <returns></returns>
        public static string SapSalesCodeSync(string storedProcName, IDataParameter[] parameters, int Times, string connectionName)
        {
            string StoreCount = string.Empty;
            string AccountCount = string.Empty;
            string ResultCount = string.Empty;
            DataSet ds = SqlConnectionHelper.RunProcedure(storedProcName, parameters, Times, connectionName);
            if (ds != null && ds.Tables.Count > 0)
            {
                DataTable dt = ds.Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {

                    StoreCount = dt.Rows[0].IsNull("StoreCount") ? "" : dt.Rows[0]["StoreCount"].ToString();
                    AccountCount = dt.Rows[0].IsNull("AccountOwnerCount") ? "" : dt.Rows[0]["AccountOwnerCount"].ToString();

                }
            }

            ResultCount = StoreCount + "!" + AccountCount;
            return ResultCount;
        }
    }
}
